﻿using Microsoft.Data.SqlClient;

namespace sqlserver_demo
{

    internal class Program
    {
        static void Main(string[] args)
        {
            test();
            Console.WriteLine("Hello, World!");
        }

        static void test()
        {
            string connStr = "Server=localhost;Database=test;User Id=yejing;Password=123456;TrustServerCertificate=True;";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    Console.WriteLine("连接成功！");
                    // 这里可以执行SQL操作

                    // 构造要插入的数据
                    TUser user = new TUser
                    {
                        Name = "张三",
                        Age = 25,
                        Mail = "zhangsan@example.com",
                        Sex = "男",
                        Addr = "北京市",
                        Phone = "13800000000"
                    };

                    // 插入SQL语句
                    string sql = "INSERT INTO tuser (name, age, mail, sex, addr, phone) VALUES (@name, @age, @mail, @sex, @addr, @phone)";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@name", user.Name);
                        cmd.Parameters.AddWithValue("@age", user.Age);
                        cmd.Parameters.AddWithValue("@mail", user.Mail);
                        cmd.Parameters.AddWithValue("@sex", user.Sex);
                        cmd.Parameters.AddWithValue("@addr", user.Addr);
                        cmd.Parameters.AddWithValue("@phone", user.Phone);

                        int rows = cmd.ExecuteNonQuery();
                        Console.WriteLine($"插入成功，受影响行数: {rows}");
                    }


                    // 查询tuser表数据
                    sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser";
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(
                                $"Id: {reader["id"]}, Name: {reader["name"]}, Age: {reader["age"]}, Mail: {reader["mail"]}, Sex: {reader["sex"]}, Addr: {reader["addr"]}, Phone: {reader["phone"]}"
                            );
                        }
                    }
                    conn.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("连接失败: " + ex.Message);
                }
            }
        }
    }

    public class TUser
    {
        private int _id;
        private string _name;
        private int _age;
        private string _mail;
        private string _sex;
        private string _addr;
        private string _phone;

        public int Id { get => _id; set => _id = value; }
        public string Name { get => _name; set => _name = value; }
        public int Age { get => _age; set => _age = value; }
        public string Mail { get => _mail; set => _mail = value; }
        public string Sex { get => _sex; set => _sex = value; }
        public string Addr { get => _addr; set => _addr = value; }
        public string Phone { get => _phone; set => _phone = value; }

    }
}
